#!/usr/bin/env python
# coding:utf-8
"""
Created on 2017/7/13 15:10

base Info
"""
__author__ = 'kumikoda'
__version__ = '1.0'

import sys

reload(sys)
sys.setdefaultencoding('utf8')

from modules import dbs
from modules import confs
from openpyxl import load_workbook

class Statistics(object):
    def __init__(self):
        self.conn, self.cur = dbs.db_connect()

    ## 业务类型月度计费
    def count_billing(self):
        query = '''
            SELECT
                business_name,
                status,
                round(sum(monthbilling), 2) AS billing,
                count(*) AS servers_used
            FROM
                cmdb
            GROUP BY
                business_name
            HAVING
                sum(monthbilling) > 0
            ORDER BY
                billing DESC '''

        try:
            self.cur.execute(query)
            data = self.cur.fetchall()
            return {'code': 61, 'message': '业务类型月计费统计成功', 'data': data}
        except Exception as e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}


    ## 服务器统计
    def count_servers(self):
        query = '''
            SELECT
                assets_user,
                country,
                IDC_type,
                room_info,
                business_name,
                count(*) AS servers_used,
                upline_time
            FROM
                cmdb AS c,
                money AS m
            WHERE
                c.assets_number = m.assets_number
            AND STATUS = "使用"
            GROUP BY
                business_name
            HAVING
                sum(m.monthbilling) > 0
            ORDER BY
                servers_used DESC'''
        try:
            self.cur.execute(query)
            data = self.cur.fetchall()
            return {'code': 62, 'message': '服务器统计成功', 'data': data}
        except Exception as e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}

    # 服务器使用总数统计列表
    def count_list_servers(self):
        query = '''
            SELECT
                c.country,
                assets_user,
                IDC_type,
                room_info,
                business_name,
                count(*) AS servers_used,
                round(sum(m.monthbilling), 2) AS billing
            FROM
                cmdb AS c,
                money AS m
            WHERE
                c.assets_number = m.assets_number
            AND STATUS = "使用"
            AND m.billing_time LIKE '2017%'
            GROUP BY
                business_name
            HAVING
                sum(m.monthbilling) > 0
            ORDER BY
                servers_used DESC'''
        try:
            self.cur.execute(query)
            data = self.cur.fetchall()
            return {'code': 62, 'message': '服务器统计成功', 'data': data}
        except Exception as e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}

    #条件查询--统计列表--旧
    def count2_servers(self, sql):
        query = '''
            SELECT
                assets_user,
                IDC_type,
                room_info,
                business_name,
                count(*) AS servers_used,
                upline_time
            FROM
                cmdb
            WHERE'''
        query += '%s' % sql
        self.cur.execute(query)
        data = self.cur.fetchall()
        return {'code': 0, 'message': 'is ok', 'data': data}

    # 服务器使用总数条件查询--统计列表
    def operation_servers(self, sql):
        query = '''
            SELECT
                c.country,
                assets_user,
                IDC_type,
                room_info,
                business_name,
                count(*) AS servers_used,
                round(sum(m.monthbilling), 2) AS billing
            FROM
                cmdb AS c,
                money AS m
            WHERE
                c.assets_number = m.assets_number
            AND'''
        query += '%s' % sql
        print query
        self.cur.execute(query)
        data = self.cur.fetchall()
        return {'code': 0, 'message': '成功', 'data': data}

    ## 月总费用统计列表
    def count_list_billing(self):
        query = '''
            SELECT
                c.country,
                assets_user,
                IDC_type,
                room_info,
                business_name,
                count(*) AS servers_used,
                round(sum(m.monthbilling), 2) AS billing
            FROM
                cmdb AS c,
                money AS m
            WHERE
                c.assets_number = m.assets_number
            AND STATUS = "使用"
            AND m.billing_time LIKE '2017%'
            GROUP BY
                c.business_name
            HAVING
                sum(m.monthbilling) > 0
            ORDER BY
                servers_used DESC'''
        try:
            self.cur.execute(query)
            data = self.cur.fetchall()
            return {'code': 62, 'message': '服务器统计成功', 'data': data}
        except Exception as e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}

    ## 月总费用条件查询--统计列表
    def operation_billing(self, sql):
        query  ='''
            SELECT
                c.country,
                assets_user,
                IDC_type,
                room_info,
                business_name,
                count(*) AS servers_used,
                round(sum(m.monthbilling), 2) AS billing
            FROM
                cmdb AS c,
                money AS m
            WHERE
                c.assets_number = m.assets_number
            AND'''
        query += '%s' % sql
        self.cur.execute(query)
        data = self.cur.fetchall()
        return {'code': 0, 'message': '成功', 'data': data}

if __name__ == '__main__':
    s = Statistics()